﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spStg_ServerRoleMembers]
#-- Purpose:		Updates Server Role/Members in the Warehouse
#--	Last Update:	01/04/2016
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spStg_ServerRoleMembers]
AS

SET NOCOUNT ON;

--- Lock the staging tables
SELECT TOP 1 * FROM [dbo].[tblStg_ServerRoleMembers] WITH (TABLOCK)

-------------------------------------------------------------------
--- UPDATE EXISTING:  Index Stats
---
UPDATE		whl
SET			[srm_databasename] = mon.[srm_databasename],
			[srm_end_date] = (CASE WHEN mon.[srm_end_date] = '1/1/1900' THEN NULL ELSE mon.[srm_end_date] END)
FROM		[dbo].[tblStg_ServerRoleMembers] mon
JOIN		[dbo].[tblWH_ServerRoleMembers] whl ON mon.[srm_server_ssd_id] = whl.[srm_server_ssd_id]
			AND mon.[srm_role_name] = whl.[srm_role_name] COLLATE SQL_Latin1_General_CP1_CI_AS
			AND mon.[srm_member_name] = whl.[srm_member_name] COLLATE SQL_Latin1_General_CP1_CI_AS
			AND mon.[srm_member_type] = whl.[srm_member_type] COLLATE SQL_Latin1_General_CP1_CI_AS
			AND ISNULL(mon.[srm_databasename], '--NA--NA--') = ISNULL(whl.[srm_databasename], '--NA--NA--') COLLATE SQL_Latin1_General_CP1_CI_AS
			AND mon.[srm_member_disabled] = whl.[srm_member_disabled]
			AND mon.[srm_start_date] = whl.[srm_start_date]
WHERE		NOT (
			ISNULL(mon.[srm_databasename], '--NA--NA--') = ISNULL(whl.[srm_databasename], '--NA--NA--') COLLATE SQL_Latin1_General_CP1_CI_AS
			AND ISNULL(mon.[srm_end_date], '1/1/1900') = ISNULL(whl.[srm_end_date], '1/1/1900')
			)

INSERT INTO [dbo].[tblWH_ServerRoleMembers]
	(
	[srm_server_ssd_id],
	[srm_role_name],
	[srm_member_name],
	[srm_member_type],
	[srm_databasename],
	[srm_member_disabled],
	[srm_start_date],
	[srm_end_date]
	)
SELECT		mon.[srm_server_ssd_id],
			mon.[srm_role_name],
			mon.[srm_member_name],
			mon.[srm_member_type],
			mon.[srm_databasename],
			mon.[srm_member_disabled],
			mon.[srm_start_date],
			mon.[srm_end_date]
FROM		[dbo].[tblStg_ServerRoleMembers] mon
LEFT JOIN	[dbo].[tblWH_ServerRoleMembers] whl ON mon.[srm_server_ssd_id] = whl.[srm_server_ssd_id]
			AND mon.[srm_role_name] = whl.[srm_role_name] COLLATE SQL_Latin1_General_CP1_CI_AS
			AND mon.[srm_member_name] = whl.[srm_member_name] COLLATE SQL_Latin1_General_CP1_CI_AS
			AND mon.[srm_member_type] = whl.[srm_member_type] COLLATE SQL_Latin1_General_CP1_CI_AS
			AND ISNULL(mon.[srm_databasename], '--NA--NA--') = ISNULL(whl.[srm_databasename], '--NA--NA--') COLLATE SQL_Latin1_General_CP1_CI_AS
			AND mon.[srm_member_disabled] = whl.[srm_member_disabled]
			AND mon.[srm_start_date] = whl.[srm_start_date]
WHERE		whl.[srm_server_ssd_id] IS NULL
ORDER BY	mon.[srm_server_ssd_id],
			mon.[srm_role_name],
			mon.[srm_member_name],
			mon.[srm_member_type],
			mon.[srm_start_date]


-------------------------------------------------------------------
--- If there is no more data in the table, truncate to reset
---		the identity value
---
TRUNCATE TABLE [dbo].[tblStg_ServerRoleMembers]

SET NOCOUNT OFF;